嗨啊!大家好,又到一個禮拜一次的這個時候了,剛剛我在選題目的時候,突然發現一件事,那就是我們SQL的題目不知不覺快要被破台了XD,所以以後SQL的題目可能會變少,要好好珍惜最後幾題,哈哈哈,不過其實不是因為那個網站的題目少,而是因為大部分的題目都被鎖起來了,他會在每個禮拜持續增加,說不定之後還會有機會分享,那接著就開始這禮拜的題目吧!
題目:Trips and Users
難易度:高
題目內容:沒錯,這一次有個Trips
表,內有計程車的出租資料,裡面包含唯一值的Id
和客戶編號Client_Id
、出租車的司機編號Driver_Id
還有出租狀態Status
和出租日期Request_at
,而出租狀態Status
有分完成completed
和司機取消cancelled_by_driver
及客戶取消cancelled_by_client
三種。
另外的Users
人員資料表記錄著包括客戶及司機,所有使用者的資料,而他有個Banned
欄位記錄著是否為受限用戶,YES為受限,NO為正常使用。
好的,那說了那麼多,這一次我們要做的事情就是,要找出出租日期2013-10-01到2013-10-03
間未受限使用者
中每一天的取消出租比例
,並四捨五入到小數點第二位,一句話把題目簡單講完,但我有標記重點XD,可以看看以下例子:
例如:Trips
表內容:
Id | Client_Id | Driver_Id | City_Id | Status | Request_at |
---|---|---|---|---|---|
1 | 1 | 10 | 1 | completed | 2013-10-01 |
2 | 2 | 11 | 1 | cancelled_by_driver | 2013-10-01 |
3 | 3 | 12 | 6 | completed | 2013-10-01 |
4 | 4 | 13 | 6 | cancelled_by_client | 2013-10-01 |
5 | 1 | 10 | 1 | completed | 2013-10-02 |
6 | 2 | 11 | 6 | completed | 2013-10-02 |
7 | 3 | 12 | 6 | completed | 2013-10-02 |
8 | 2 | 12 | 12 | completed | 2013-10-03 |
9 | 3 | 10 | 12 | completed | 2013-10-03 |
10 | 4 | 13 | 12 | cancelled_by_driver | 2013-10-03 |
Users 表內容: |
|||||
Users_Id | Banned | Role | |||
------------- | ------------- | ------------- | |||
1 | No | client | |||
2 | Yes | client | |||
3 | No | client | |||
4 | No | client | |||
10 | No | driver | |||
11 | No | driver | |||
12 | No | driver | |||
13 | No | driver | |||
查詢結果: |
Day | Cancellation Rate
------------- | -------------
2013-10-01 | 0.33
2013-10-02 | 0.00
2013-10-03 | 0.50
Cancellation Rate
欄位內的資料就是個日期的未受限用戶取消比例,例如2013-10-01這個日期中有四筆資料,但是Client_Id
為2,他是受限用戶,不列入計算,所以2013-10-01剩下三筆出租資料,而三筆資料中又有一筆資料取消,所以取消的比例為1/3四捨五入到小數點第二位為0.33,其他兩天以此類推。
這一題解得還滿順的,以下解法:
/*(1)這邊查詢日期欄位資料*/
SELECT t.Request_at Day,
/*(8)因為轉成FLOAT的關係,所以用ROUND把小數位數控制在兩位*/
ROUND(
/*(6)這一層的SUM把內層的資料加總起來,得到取消資料的數量*/
SUM(
/*(5)內層先處理,用CASE WHEN判斷把取消的資料變為1,其餘為0*/
CASE WHEN t.Status = 'cancelled_by_driver' OR t.Status = 'cancelled_by_client' THEN 1 ELSE 0 END
)
/*(7)這邊除上用COUNT取得該日期的所有資料數目,
我把總資料數轉成FOLAT型態,這樣他除起來就會有小數點*/
/CONVERT(FLOAT,COUNT(*))
,2) [Cancellation Rate]
FROM Trips t
/*(3)只撈出不等於受限資料的使用者*/
WHERE t.Client_Id NOT IN (SELECT Users_Id FROM Users WHERE Banned = 'Yes')
/*(4)這邊指定日期,因為題目只要這三天的資料*/
AND t.Request_at BETWEEN '2013-10-01' AND '2013-10-03'
/*(2)用GROUP BY讓日期可以把每天的資料作為一個群組,讓我們去計算*/
GROUP BY t.Request_at
然後這次的成績是...我就不貼了XD,哈哈哈,其實不是因為太爛啦,只是我覺得這個出現的區間太大了,這禮拜我1%~96%都跑到過,哈哈哈,個人是覺得和網路速度應該有關係,所以就當參考用就好!
啊啊,其實看了文章感覺這一題的解法大家都差不多,只有些是另外JOIN
子查詢計算資料,而我是直接在SELECT
寫完,而這次有沒有大大用一樣的方式寫呢?有!而且就在第一則!!不過是MySQL版本的,貼出來給大家看看XD,因為只是換個語言而已,所以我只註解標記的地方,哈哈哈!
原解答網址
SELECT Request_at as Day,
ROUND(
COUNT(
/*因為MySQL沒有CASE WHEN取而代之用IF判斷,如果不等於就是TRUE(1),不然就NULL(0)*/
IF(Status != 'completed', TRUE, NULL))
/ COUNT(*)
, 2) AS 'Cancellation Rate'
FROM Trips
WHERE (Request_at BETWEEN '2013-10-01' AND '2013-10-03')
AND Client_id NOT IN (SELECT Users_Id FROM Users WHERE Banned = 'Yes')
GROUP BY Request_at;
對耶!在幫忙下註解的時候赫然發現,我就直接判斷completed
就好了,我還去判斷兩種狀態,真的是一心只想著取消,都忘記換個方向思考了XD,不過我就不改我的版本了,看版上大大有沒有更棒的解法,哈哈哈!
這次我也提供了現在風靡板上的MSSQL連結讓各位大大去玩XD。
話說文章的最後大家有沒有發現這禮拜的難度居然是高級的,哈哈哈,我一直都沒提想說這樣解起來會不會比較簡單,才不會有慘了,這次一定很難,的心理作用XD,啊選擇困難的原因只是單純想快點全破而已,哈哈哈,如果有一起做的大大,現在的進度應該也和我一樣,快解完SQL的題目了,像下面這張圖一樣:
搞定這題後,大家一起破台SQL的困難級題目
如果以上文章中有任何不懂的,或是解釋錯誤,都麻煩各位大大留言告訴我,我會盡快改進,謝謝大家
又到大大每禮拜一挑戰
我使用sql-server來做題目
一開始解法跟大大都差不多
後面就想說玩點特別的使用distinct
+ sum over
相對效能一定會比較差XDD
--把Banned的使用者篩選掉
select distinct [Request_at] [Day]
--只需要取到小數點二位
,round(
--算取消率,特別使用sum少一次轉型
sum(case when [Status]<>'completed' then 1.00 else 0.00 end)
over (partition by [Request_at])
/count(1) over (partition by [Request_at])
,2) [Cancellation_Rate]
from Trips
where 1=1
--篩選 只要10/01 - 03的資料
and [Request_at] between '2013-10-01' and '2013-10-03'
--把Banned的使用者篩選掉
and Client_Id not in (
select Users_Id from Users
where Banned = 'Yes'
)
;
結果:
Day | Cancellation_Rate |
---|---|
2013-10-01 | 0.330000 |
2013-10-02 | 0.000000 |
2013-10-03 | 0.500000 |
哈哈,這樣子就不用GROUP BY
了!
而且大大直接在CASE WHEN
中指定小數點1.00,這我也沒想到XD
這樣就像註解說的,可以少一次轉型!
我想這些SQL的問題已經難不倒大大們了,
以後可能要比誰寫出最有趣的寫法
沒想到判斷 completed
和直接轉型 1.00
+1
MS SQL 連結
SELECT
CONVERT(NVARCHAR(10), Request_at, 120)AS [Day],
ROUND(
SUM(CASE WHEN [Status]='cancelled_by_client' OR
[Status]='cancelled_by_driver' THEN 1 ELSE 0 END) /
CAST(COUNT(Id) AS float), 2
) AS [Cancellation Rate]
FROM @Trips AS A
LEFT JOIN @Users AS B ON B.Users_Id=A.Client_Id
WHERE Request_at >= '2013-10-01' AND Request_at <= '2013-10-03' AND
Banned = 'No'
GROUP BY Request_at
這次輕輕鬆鬆的感覺嗎
哈哈哈,這次蠻輕鬆的,這樣也不錯,我可以留點腦力去想 Code Jam 的題目。
我有看到你要再寫一次XD,
這次的時間應該充裕很多,記得上次你是花一個晚上搞定的XD
看大大要不要整理後再發文一次,
換我在文章下面分享解法
好啊,如果我真的有找到更好解法的話。
哈哈哈,好哦!
我現在在想第三題要怎麼解,
我今天晚上先留言另外兩題